package com.company.ems.mapper;

import com.company.ems.model.Employee;
import org.apache.ibatis.annotations.*;

import java.util.List;

public interface EmployeeMapper {
    // 1. 新增员工（单参数为实体类，无需@Param）
    @Insert("INSERT INTO employees(name, department, position, salary, email, phone, hire_date, created_by) " +
            "VALUES(#{name}, #{department}, #{position}, #{salary}, #{email}, #{phone}, #{hireDate}, #{createdBy})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(Employee employee);

    // 2. 按ID删除（单参数，无需@Param）
    @Delete("DELETE FROM employees WHERE id = #{id}")
    int deleteById(Integer id);

    // 3. 按ID更新（单参数为实体类，无需@Param）
    @Update("UPDATE employees SET " +
            "name=#{name}, department=#{department}, position=#{position}, " +
            "salary=#{salary}, email=#{email}, phone=#{phone}, hire_date=#{hireDate} " +
            "WHERE id = #{id}")
    int updateById(Employee employee);

    // 4. 按ID查询（单参数，无需@Param）
    @Select("SELECT id, name, department, position, salary, email, phone, " +
            "hire_date AS hireDate, created_by AS createdBy, " +
            "created_at AS createdAt, updated_at AS updatedAt " +
            "FROM employees WHERE id = #{id}")
    Employee selectById(Integer id);

    // 5. 分页查询（多参数，加@Param明确参数名）
    @Select("SELECT id, name, department, position, salary, email, phone, " +
            "hire_date AS hireDate, created_by AS createdBy, " +
            "created_at AS createdAt, updated_at AS updatedAt " +
            "FROM employees ORDER BY hire_date DESC LIMIT #{startIndex}, #{pageSize}")
    List<Employee> selectPage(
            @Param("startIndex") Integer startIndex,  // 对应SQL的#{startIndex}
            @Param("pageSize") Integer pageSize      // 对应SQL的#{pageSize}
    );

    // 6. 查询总记录数（无参数，无需@Param）
    @Select("SELECT COUNT(*) FROM employees")
    int selectTotalCount();

    // 7. 查询所有部门（无参数，无需@Param）
    @Select("SELECT DISTINCT department FROM employees ORDER BY department")
    List<String> selectAllDepartments();

    // 8. 按部门查询职位（多参数，加@Param明确参数名）
    @Select("SELECT DISTINCT position FROM employees WHERE department = #{dept} ORDER BY position")
    List<String> selectPositionsByDepartment(
            @Param("dept") String department  // 对应SQL的#{dept}
    );
}